﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using YiTu.DBUtility;
namespace TOUR.SZHOME.DAL
{
    /// <summary>
    /// 数据表Projects_New的数据操作类
    /// </summary>
    public partial class Projects_NewDAL
    {
        #region ConstVariables
        private const string C_TABLE_NAME = "Projects_New";
        private const string C_SP_PROJECTS_NEW_FIELDS = "[DTID],[XMID],[DTBT],[DTDZ],[FBSJ]";
        private const string C_SP_PROJECTS_NEW_INSERT = "INSERT INTO [Projects_New]([XMID],[DTBT],[DTDZ],[FBSJ]) VALUES(@XMID,@DTBT,@DTDZ,@FBSJ);SET @DTID = SCOPE_IDENTITY();";
        private const string C_SP_PROJECTS_NEW_UPDATE = "UPDATE [Projects_New] SET [XMID]=@XMID,[DTBT]=@DTBT,[DTDZ]=@DTDZ,[FBSJ]=@FBSJ WHERE [DTID] = @DTID";
        private const string C_SP_PROJECTS_NEW_DELETE = "DELETE [Projects_New] WHERE [DTID] = @DTID";
        private const string C_SP_PROJECTS_NEW_GET = "SELECT [DTID],[XMID],[DTBT],[DTDZ],[FBSJ] FROM [Projects_New] WHERE [DTID] = @DTID";
        #endregion

        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["TravelProject"].ConnectionString;
            }
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        private Projects_NewDAL() { }

        /// <summary>
        /// 向数据表中插入一条新记录
        /// </summary>
        /// <param name="entity">Entity.Projects_NewEntity实体类</param>
        /// <remarks>如果表存在自增长字段，插入记录成功后自增长字段值会更新至实体</remarks>
        public static bool Insert(Entity.Projects_NewEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            SqlParameter id_DTID = SqlHelper.CreateParam("@DTID", SqlDbType.Int, 0, ParameterDirection.Output, null);
            commandParms.Add(id_DTID);
            commandParms.Add(SqlHelper.CreateParam("@XMID", SqlDbType.Int, 0, ParameterDirection.Input, entity.XMID));
            commandParms.Add(SqlHelper.CreateParam("@DTBT", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.DTBT));
            commandParms.Add(SqlHelper.CreateParam("@DTDZ", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.DTDZ));
            commandParms.Add(SqlHelper.CreateParam("@FBSJ", SqlDbType.DateTime, 0, ParameterDirection.Input, entity.FBSJ));
            
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_NEW_INSERT, commandParms) > 0 ? true : false;
            //entity.DTID = Convert.ToInt32(id_DTID.Value);
        }

        /// <summary>
        /// 获取数据库一条记录实体(根据主键条件)
        /// </summary>
        /// <param name="dTID">新闻动态ID</param>
        /// <returns>Entity.Projects_NewEntity实体类</returns>
        public static Entity.Projects_NewEntity GetByDTID(int dTID)
        {
            Entity.Projects_NewEntity entity = null;
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@DTID", SqlDbType.Int, 0, ParameterDirection.Input, dTID));

            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, C_SP_PROJECTS_NEW_GET, commandParms))
            {
                if (reader.Read())
                {
                    entity = ConvertToEntityFromDataReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 更新数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="entity">Entity.Projects_NewEntity实体类</param>
        public static bool Update(Entity.Projects_NewEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();

            commandParms.Add(SqlHelper.CreateParam("@DTID", SqlDbType.Int, 0, ParameterDirection.Input, entity.DTID));
            commandParms.Add(SqlHelper.CreateParam("@XMID", SqlDbType.Int, 0, ParameterDirection.Input, entity.XMID));
            commandParms.Add(SqlHelper.CreateParam("@DTBT", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.DTBT));
            commandParms.Add(SqlHelper.CreateParam("@DTDZ", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.DTDZ));
            commandParms.Add(SqlHelper.CreateParam("@FBSJ", SqlDbType.DateTime, 0, ParameterDirection.Input, entity.FBSJ));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_NEW_UPDATE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 删除数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="dTID">新闻动态ID</param>
        public static bool Delete(int dTID)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@DTID", SqlDbType.Int, 0, ParameterDirection.Input, dTID));
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_NEW_DELETE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int startRowIndex, int maximumRows)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_PROJECTS_NEW_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, startRowIndex, maximumRows);
        }


        /// <summary>
        /// 按条件查询数据表,返回 Entity.Projects_NewEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        public static List<Entity.Projects_NewEntity> Search(string whereClause, string orderBy, int startRowIndex, int maximumRows)
        {
            List<Entity.Projects_NewEntity> list = new List<Entity.Projects_NewEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_PROJECTS_NEW_FIELDS, whereClause, orderBy, startRowIndex, maximumRows))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int rowsToReturn)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_PROJECTS_NEW_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, rowsToReturn);
        }

        /// <summary>
        /// 按条件查询数据表,返回 Entity.Projects_NewEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        public static List<Entity.Projects_NewEntity> Search(string whereClause, string orderBy, int rowsToReturn)
        {
            List<Entity.Projects_NewEntity> list = new List<Entity.Projects_NewEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_PROJECTS_NEW_FIELDS, whereClause, orderBy, rowsToReturn))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件获取记录数量
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <returns>int整型数据</returns>
        public static int SearchCount(string whereClause)
        {
            return SqlListHepler.SearchCount(ConnectionString, C_TABLE_NAME, whereClause);
        }

        /// <summary>
        /// 转换DataRow类型数据记录为实体
        /// </summary>
        private static Entity.Projects_NewEntity ConvertToEntityFromDataRow(DataRow row)
        {
            Entity.Projects_NewEntity entity = new Entity.Projects_NewEntity();
            entity.DTID = Convert.ToInt32(row["DTID"]);
            entity.XMID = Convert.ToInt32(row["XMID"]);
            entity.DTBT = row["DTBT"].ToString();
            if (Convert.IsDBNull(row["DTDZ"])) entity.DTDZ = null; else entity.DTDZ = row["DTDZ"].ToString();
            if (Convert.IsDBNull(row["FBSJ"])) entity.FBSJ = null; else entity.FBSJ = Convert.ToDateTime(row["FBSJ"]);

            return entity;
        }

        /// <summary>
        /// 转换SqlDataReader类型数据记录为实体
        /// </summary>
        private static Entity.Projects_NewEntity ConvertToEntityFromDataReader(SqlDataReader reader)
        {
            Entity.Projects_NewEntity entity = new Entity.Projects_NewEntity();
            entity.DTID = Convert.ToInt32(reader["DTID"]);
            entity.XMID = Convert.ToInt32(reader["XMID"]);
            entity.DTBT = reader["DTBT"].ToString();
            if (Convert.IsDBNull(reader["DTDZ"])) entity.DTDZ = null; else entity.DTDZ = reader["DTDZ"].ToString();
            if (Convert.IsDBNull(reader["FBSJ"])) entity.FBSJ = null; else entity.FBSJ = Convert.ToDateTime(reader["FBSJ"]);

            return entity;
        }

    }
}
